﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Drawing;
using System.Data;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
namespace ACLCTaguigEnrollmentSystem.UserControls
{
    public partial class uc_AdmissionStudentRegistration : UserControl
    {

#region Initialization

        SqlConnection conn = new SqlConnection(Classes.ConnectionString.connString);

#endregion

        public uc_AdmissionStudentRegistration()
        {
            InitializeComponent();
        }

        private void AdmissionStudentRegistration_Load(object sender, EventArgs e)
        {
            SqlCommand cmdStudent = new SqlCommand("SELECT StudentID FROM Students WHERE StudentNo = '" + Classes.Variable.StudentNo + "'", conn);
            SqlDataAdapter daStudent = new SqlDataAdapter(cmdStudent);
            DataTable dtStudent = new DataTable();
            daStudent.Fill(dtStudent);

            //DataPopulation
            cbSchoolYear();
            cbTerm();
            cbYear();
            cbAdmissionType();
            cbCourse();
            cbSex();
            cbCivilStatus();
            cbAdvertisement();
            cbCitizenship();
            cbRelatives();
            cbReligioin();
            if (dtStudent.Rows.Count == 0)
            {
                //Business Rules
                AdmissionTypeCondition(); //Not Working
                StudentNumberAutoNumber();
            }
            else
            {
                SelectStudent();
            }


           
        }

        private void tsb_StudentSave_Click(object sender, EventArgs e)
        {
            CheckAddedStudent();
        }

        private void btn_StudentNoManual_Click(object sender, EventArgs e)
        {
            txt_StudentNo.Clear();
        }

 #region  Methods

        #region BusinessRules

        private void AdmissionTypeCondition()
        {
            if (cb_AdmissionType.SelectedText == "Freshmen")
            {
                cb_Year.Enabled = false;
            }
            else
            {
                cb_Year.Enabled = true;
            }
        }

        private void StudentNumberAutoNumber()
        {
            string yy = DateTime.Now.ToString("yy");
            string mm = DateTime.Now.ToString("MMd");

            SqlCommand cmd = new SqlCommand("SELECT TOP 1 FORMAT(CAST(RIGHT(StudentNo,4) + 1 as int),'000#') AS StudentNoInc, FORMAT(CAST(LEFT(StudentNo,2) as int),'#') as yy  FROM Students ORDER BY 1 DESC", conn);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable();
            da.Fill(dt);
            if (dt.Rows[0].ItemArray[1].ToString() == yy)
            {
                txt_StudentNo.Text = yy + "-" + (mm + dt.Rows[0].ItemArray[0].ToString());
            }
            else
            {
                int num = 0001;
                txt_StudentNo.Text = yy + "-" + (mm + num.ToString("D4"));
            }
            conn.Open();

            conn.Close();
        }

        private void CheckAddedStudent()
        {
            SqlCommand cmdCheck = new SqlCommand("SELECT StudentNo FROM Students WHERE StudentNo = '" + txt_StudentNo.Text + "'", conn);
            SqlDataAdapter daCheck = new SqlDataAdapter(cmdCheck);
            DataTable dtCheck = new DataTable();
            daCheck.Fill(dtCheck);

            if (dtCheck.Rows.Count == 0)
            {

                AddNewStudent();
                AddEnrolledStudent();
                MessageBox.Show("The new student information is successfully added.");
            }
            else
            {
                EditStudent();
                EditEnrolledStudent();
                MessageBox.Show("Successfully update the date");
            }
        }
        
#endregion

        #region ComboBoxData

        private void cbSchoolYear()
        {
            SqlCommand cmdSchoolYear = new SqlCommand("SELECT SchoolYearID, Cast(SchoolYearFrom as varchar(30)) + ' - ' + CAST(SchoolYearTo as varchar(30)) as SchoolYear FROM SchoolYear", conn);
            SqlDataAdapter daSchoolYear = new SqlDataAdapter(cmdSchoolYear);
            DataSet dsSchoolYear = new DataSet();
            daSchoolYear.Fill(dsSchoolYear);
            conn.Open();
            cb_SchoolYear.DataSource = dsSchoolYear.Tables[0];
            cb_SchoolYear.ValueMember = "SchoolYearID";
            cb_SchoolYear.DisplayMember = "SchoolYear";
            conn.Close();
        }

        private void cbTerm()
        {
            SqlCommand cmdTerm = new SqlCommand("SELECT TermID, TermName FROM Terms", conn);
            SqlDataAdapter daTerm = new SqlDataAdapter(cmdTerm);
            DataSet dsTerm = new DataSet();
            daTerm.Fill(dsTerm);
            conn.Open();
            cb_Term.DataSource = dsTerm.Tables[0];
            cb_Term.ValueMember = "TermID";
            cb_Term.DisplayMember = "TermName";
            conn.Close();
        }

        private void cbYear()
        {
            SqlCommand cmdYear = new SqlCommand("SELECT YearID, YearDesc FROM Year", conn);
            SqlDataAdapter daYear = new SqlDataAdapter(cmdYear);
            DataSet dsYear = new DataSet();
            daYear.Fill(dsYear);
            conn.Open();
            cb_Year.DataSource = dsYear.Tables[0];
            cb_Year.ValueMember = "YearID";
            cb_Year.DisplayMember = "YearDesc";
            conn.Close();
        }

        private void cbCourse()
        {
            SqlCommand cmdCourse = new SqlCommand("SELECT c.CourseID, (d.DegreeCode + ' ' + c.CourseName) as Course  FROM COurses c LEFT JOIN Degree d ON c.DegreeId = d.DegreeID", conn);
            SqlDataAdapter daCourse = new SqlDataAdapter(cmdCourse);
            DataSet dsCourse = new DataSet();
            daCourse.Fill(dsCourse);
            conn.Open();
            cb_Course.DataSource = dsCourse.Tables[0];
            cb_Course.ValueMember = "CourseID";
            cb_Course.DisplayMember = "Course";
            conn.Close();
        }

        private void cbAdmissionType()
        {
            SqlCommand cmdAdmissionType = new SqlCommand("SELECT AdmissionTypeID, AdmissionTypeName FROM AdmissionType", conn);
            SqlDataAdapter daAdmissionType = new SqlDataAdapter(cmdAdmissionType);
            DataSet dsAdmissionType = new DataSet();
            daAdmissionType.Fill(dsAdmissionType);
            conn.Open();
            cb_AdmissionType.DataSource = dsAdmissionType.Tables[0];
            cb_AdmissionType.ValueMember = "AdmissionTypeID";
            cb_AdmissionType.DisplayMember = "AdmissionTypeName";
            conn.Close();
        }

        private void cbSex()
        {
            SqlCommand cmdSex = new SqlCommand("SELECT SexName FROM Sex", conn);
            SqlDataAdapter daSex = new SqlDataAdapter(cmdSex);
            DataSet dsSex = new DataSet();
            daSex.Fill(dsSex);

            conn.Open();
            cb_Sex.DataSource = dsSex.Tables[0];
            cb_Sex.ValueMember = "SexName";
            cb_Sex.DisplayMember = "SexName";
            conn.Close();
        }

        private void cbCivilStatus()
        {
            SqlCommand cmdCivilStatus = new SqlCommand("SELECT CivilStatusNAme FROM CivilStatus", conn);
            SqlDataAdapter daCivilStatus = new SqlDataAdapter(cmdCivilStatus);
            DataSet dsCivilStatus = new DataSet();
            daCivilStatus.Fill(dsCivilStatus);

            conn.Open();
            cb_CivilStatus.DataSource = dsCivilStatus.Tables[0];
            cb_CivilStatus.ValueMember = "CivilStatusNAme";
            cb_CivilStatus.DisplayMember = "CivilStatusNAme";
            conn.Close();
        }

        private void cbCitizenship()
        {
            SqlCommand cmdCitizenship = new SqlCommand("SELECT CitizenshipName FROM Citizenship", conn);
            SqlDataAdapter daCitizenship = new SqlDataAdapter(cmdCitizenship);
            DataSet dsCitizenship = new DataSet();
            daCitizenship.Fill(dsCitizenship);

            conn.Open();
            cb_Citizenship.DataSource = dsCitizenship.Tables[0];
            cb_Citizenship.ValueMember = "CitizenshipName";
            cb_Citizenship.DisplayMember = "CitizenshipName";
            conn.Close();
        }

        private void cbReligioin()
        {
            SqlCommand cmdReligion = new SqlCommand("SELECT ReligionName FROM Religion", conn);
            SqlDataAdapter daReligion = new SqlDataAdapter(cmdReligion);
            DataSet dsReligion = new DataSet();
            daReligion.Fill(dsReligion);

            conn.Open();
            cb_Religion.DataSource = dsReligion.Tables[0];
            cb_Religion.ValueMember = "ReligionName";
            cb_Religion.DisplayMember = "ReligionName";
            conn.Close();
        }

        private void cbAdvertisement()
        {
            SqlCommand cmdAdvertisement = new SqlCommand("SELECT AdvertisementName FROM Advertisement", conn);
            SqlDataAdapter daAdvertisement = new SqlDataAdapter(cmdAdvertisement);
            DataSet dsAdvertisement = new DataSet();
            daAdvertisement.Fill(dsAdvertisement);

            conn.Open();
            cb_Advertisement.DataSource = dsAdvertisement.Tables[0];
            cb_Advertisement.ValueMember = "AdvertisementName";
            cb_Advertisement.DisplayMember = "AdvertisementName";
            conn.Close();
        }

        private void cbRelatives()
        {
            SqlCommand cmdRelatives = new SqlCommand("SELECT RelativesType FROM Relatives", conn);
            SqlDataAdapter daRelatives = new SqlDataAdapter(cmdRelatives);
            DataSet dsRelatives = new DataSet();
            daRelatives.Fill(dsRelatives);

            conn.Open();
            cb_RelativesAbroad.DataSource = dsRelatives.Tables[0];
            cb_RelativesAbroad.ValueMember = "RelativesType";
            cb_RelativesAbroad.DisplayMember = "RelativesType";
            conn.Close();
        }
        #endregion

        #region Data

        private void AddNewStudent()
        {
                SqlCommand cmd = new SqlCommand("dbo.spStudent_Insert", conn);
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                cmd.CommandType = CommandType.StoredProcedure;

                conn.Open();

                cmd.Parameters.AddWithValue("@AdmissionTypeID", cb_AdmissionType.SelectedValue);
                cmd.Parameters.AddWithValue("@StudentNo", txt_StudentNo.Text);
                cmd.Parameters.AddWithValue("@LastName", txt_LastName.Text);
                cmd.Parameters.AddWithValue("@FirstName", txt_FirstName.Text);
                cmd.Parameters.AddWithValue("@MiddleName", txt_MiddleName.Text);
                cmd.Parameters.AddWithValue("@Sex", cb_Sex.SelectedValue);
                cmd.Parameters.AddWithValue("@CivilStatus", cb_CivilStatus.SelectedValue);
                cmd.Parameters.AddWithValue("@PresentAddress", txt_PresentAddress.Text);
                cmd.Parameters.AddWithValue("@ProvincialAddress", txt_ProvincialAddress.Text);
                cmd.Parameters.AddWithValue("@TelephoneNo", txt_TelephoneNo.Text);
                cmd.Parameters.AddWithValue("@MobileNo", txt_MobileNo.Text);
                cmd.Parameters.AddWithValue("@EmailAddress", txt_EmailAddress.Text);
                cmd.Parameters.Add("@Birthdate", SqlDbType.Date).Value = dtp_BirthDate.Text;
                cmd.Parameters.AddWithValue("@BirthPlace", txt_BirthPlace.Text);
                cmd.Parameters.AddWithValue("@Citizenship", cb_Citizenship.SelectedValue);
                cmd.Parameters.AddWithValue("@Religion", cb_Religion.SelectedValue);
                cmd.Parameters.AddWithValue("@LastSchoolAttended", txt_LastSchoolAttended.Text);
                cmd.Parameters.AddWithValue("@SchoolAddress", txt_SchoolAddress.Text);
                cmd.Parameters.AddWithValue("@FathersName", txt_FathersName.Text);
                cmd.Parameters.AddWithValue("@FathersOccupation", txt_FathersOccupation.Text);
                cmd.Parameters.AddWithValue("@MothersName", txt_MothersName.Text);
                cmd.Parameters.AddWithValue("@MothersOccupation", txt_MothersOccupation.Text);
                cmd.Parameters.AddWithValue("@DoYouHaveRelativeAbroad", cb_RelativesAbroad.SelectedValue);
                cmd.Parameters.AddWithValue("@HowDidYouFindACLCCollegeTaguig", cb_Advertisement.SelectedValue);

                cmd.ExecuteNonQuery();
                conn.Close();

        }

        private void AddEnrolledStudent()
        {
            SqlCommand cmdStudent = new SqlCommand("SELECT StudentID FROM Students WHERE StudentNo = '" + txt_StudentNo.Text + "'", conn);
            SqlDataAdapter daStudent = new SqlDataAdapter(cmdStudent);
            DataTable dtStudent = new DataTable();
            daStudent.Fill(dtStudent);

            SqlCommand cmd = new SqlCommand("dbo.spEnrollment_Insert ", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlDataAdapter da = new SqlDataAdapter(cmd);

            conn.Open();
            cmd.Parameters.AddWithValue("@StudentID", dtStudent.Rows[0].ItemArray[0].ToString());
            cmd.Parameters.AddWithValue("@Course", cb_Course.SelectedValue);
            cmd.Parameters.AddWithValue("@Year", cb_Year.SelectedValue);
            cmd.Parameters.AddWithValue("@Term", cb_Term.SelectedValue);
            cmd.Parameters.AddWithValue("@SchoolYear", cb_SchoolYear.SelectedValue);
            cmd.ExecuteNonQuery();
            conn.Close();
        }

        private void EditStudent()
        {
            SqlCommand cmdStudent = new SqlCommand("SELECT StudentID FROM Students WHERE StudentNo = '" + Classes.Variable.StudentNo + "'", conn);
            SqlDataAdapter daStudent = new SqlDataAdapter(cmdStudent);
            DataTable dtStudent = new DataTable();
            daStudent.Fill(dtStudent);


            SqlCommand cmd = new SqlCommand("dbo.spStudent_Update", conn);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            cmd.CommandType = CommandType.StoredProcedure;
           
            conn.Open();
            cmd.Parameters.AddWithValue("@StudentID", dtStudent.Rows[0].ItemArray[0].ToString());
            cmd.Parameters.AddWithValue("@AdmissionTypeID", cb_AdmissionType.SelectedValue);
            cmd.Parameters.AddWithValue("@StudentNo", txt_StudentNo.Text);
            cmd.Parameters.AddWithValue("@UniversalStudentNo", txt_UniversalStudentNo.Text);
            cmd.Parameters.AddWithValue("@LastName", txt_LastName.Text);
            cmd.Parameters.AddWithValue("@FirstName", txt_FirstName.Text);
            cmd.Parameters.AddWithValue("@MiddleName", txt_MiddleName.Text);
            cmd.Parameters.AddWithValue("@Sex", cb_Sex.SelectedValue);
            cmd.Parameters.AddWithValue("@CivilStatus", cb_CivilStatus.SelectedValue);
            cmd.Parameters.AddWithValue("@PresentAddress", txt_PresentAddress.Text);
            cmd.Parameters.AddWithValue("@ProvincialAddress", txt_ProvincialAddress.Text);
            cmd.Parameters.AddWithValue("@TelephoneNo", txt_TelephoneNo.Text);
            cmd.Parameters.AddWithValue("@MobileNo", txt_MobileNo.Text);
            cmd.Parameters.AddWithValue("@EmailAddress", txt_EmailAddress.Text);
            cmd.Parameters.Add("@Birthdate", SqlDbType.Date).Value = dtp_BirthDate.Text;
            cmd.Parameters.AddWithValue("@BirthPlace", txt_BirthPlace.Text);
            cmd.Parameters.AddWithValue("@Citizenship", cb_Citizenship.SelectedValue);
            cmd.Parameters.AddWithValue("@Religion", cb_Religion.SelectedValue);
            cmd.Parameters.AddWithValue("@LastSchoolAttended", txt_LastSchoolAttended.Text);
            cmd.Parameters.AddWithValue("@SchoolAddress", txt_SchoolAddress.Text);
            cmd.Parameters.AddWithValue("@FathersName", txt_FathersName.Text);
            cmd.Parameters.AddWithValue("@FathersOccupation", txt_FathersOccupation.Text);
            cmd.Parameters.AddWithValue("@MothersName", txt_MothersName.Text);
            cmd.Parameters.AddWithValue("@MothersOccupation", txt_MothersOccupation.Text);
            cmd.Parameters.AddWithValue("@DoYouHaveRelativeAbroad", cb_RelativesAbroad.SelectedValue);
            cmd.Parameters.AddWithValue("@HowDidYouFindACLCCollegeTaguig", cb_Advertisement.SelectedValue);

            cmd.ExecuteNonQuery();
            conn.Close();
        }

        private void EditEnrolledStudent()
        {

            SqlCommand cmdStudent = new SqlCommand("SELECT StudentID FROM Students WHERE StudentNo = '" + Classes.Variable.StudentNo + "'", conn);
            SqlDataAdapter daStudent = new SqlDataAdapter(cmdStudent);
            DataTable dtStudent = new DataTable();
            daStudent.Fill(dtStudent);

            
            SqlCommand cmd = new SqlCommand("dbo.spEnrollment_Update ", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlDataAdapter da = new SqlDataAdapter(cmd);

            conn.Open();
            cmd.Parameters.AddWithValue("@StudentID", dtStudent.Rows[0].ItemArray[0].ToString());
            cmd.Parameters.AddWithValue("@Course", cb_Course.SelectedValue);
            cmd.Parameters.AddWithValue("@Year", cb_Year.SelectedValue);
            cmd.Parameters.AddWithValue("@Term", cb_Term.SelectedValue);
            cmd.Parameters.AddWithValue("@SchoolYear", cb_SchoolYear.SelectedValue);
            cmd.ExecuteNonQuery();
            conn.Close();
        }

        private void SelectStudent()
        {
            SqlCommand cmdStudent = new SqlCommand("SELECT StudentID FROM Students WHERE StudentNo = '" + Classes.Variable.StudentNo + "'", conn);
            SqlDataAdapter daStudent = new SqlDataAdapter(cmdStudent);
            DataTable dtStudent = new DataTable();
            daStudent.Fill(dtStudent);

            SqlCommand cmd = new SqlCommand("dbo.spStudent_SelectViewEdit", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@StudentID", dtStudent.Rows[0].ItemArray[0].ToString());
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable();
            da.Fill(dt);

            conn.Open();
            
            cb_AdmissionType.SelectedValue = dt.Rows[0].ItemArray[0].ToString();
            cb_SchoolYear.SelectedValue = dt.Rows[0].ItemArray[1].ToString();
            cb_Term.SelectedValue = dt.Rows[0].ItemArray[2].ToString();
            cb_Year.SelectedValue = dt.Rows[0].ItemArray[3].ToString();
            cb_Course.SelectedValue = dt.Rows[0].ItemArray[4].ToString();
            txt_StudentNo.Text = dt.Rows[0].ItemArray[5].ToString();
            txt_UniversalStudentNo.Text = dt.Rows[0].ItemArray[6].ToString();
            txt_LastName.Text = dt.Rows[0].ItemArray[7].ToString();
            txt_FirstName.Text = dt.Rows[0].ItemArray[8].ToString();
            txt_MiddleName.Text = dt.Rows[0].ItemArray[9].ToString();
            cb_Sex.SelectedValue = dt.Rows[0].ItemArray[10].ToString();
            cb_CivilStatus.SelectedValue = dt.Rows[0].ItemArray[11].ToString();
            txt_PresentAddress.Text = dt.Rows[0].ItemArray[12].ToString();
            txt_ProvincialAddress.Text = dt.Rows[0].ItemArray[13].ToString();
            txt_TelephoneNo.Text = dt.Rows[0].ItemArray[14].ToString();
            txt_MobileNo.Text = dt.Rows[0].ItemArray[15].ToString();
            txt_EmailAddress.Text = dt.Rows[0].ItemArray[16].ToString();
            dtp_BirthDate.Text = dt.Rows[0].ItemArray[17].ToString();
            txt_BirthPlace.SelectedText = dt.Rows[0].ItemArray[18].ToString();
            cb_Citizenship.SelectedValue= dt.Rows[0].ItemArray[19].ToString();
            cb_Religion.SelectedValue = dt.Rows[0].ItemArray[20].ToString();
            txt_LastSchoolAttended.Text = dt.Rows[0].ItemArray[21].ToString();
            txt_SchoolAddress.Text = dt.Rows[0].ItemArray[22].ToString();
            txt_FathersName.Text = dt.Rows[0].ItemArray[23].ToString();
            txt_FathersOccupation.Text = dt.Rows[0].ItemArray[24].ToString();
            txt_MothersName.Text = dt.Rows[0].ItemArray[25].ToString();
            txt_MothersOccupation.Text = dt.Rows[0].ItemArray[26].ToString();
            cb_RelativesAbroad.SelectedValue = dt.Rows[0].ItemArray[27].ToString();
            cb_Advertisement.SelectedValue = dt.Rows[0].ItemArray[28].ToString();
            conn.Close();

        }

        #endregion

#endregion


    }
}
